﻿

USE TestDataBase

BEGIN TRANSACTION;

CREATE SCHEMA [structure] AUTHORIZATION [dbo];
CREATE TABLE [structure].[Item] ([Id] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [ParentId] UNIQUEIDENTIFIER NULL, [ExternalId] VARCHAR (100) NULL,[PublishedVersion] INT NULL, [ItemClaimId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [Href] VARCHAR (2000) NOT NULL);
CREATE TABLE [structure].[ItemClaim] ([Id] UNIQUEIDENTIFIER NOT NULL, [ClaimType] VARCHAR (255) NOT NULL, [ClaimValue] VARCHAR (50) NOT NULL, [AccessFlags] INT DEFAULT ((0)) NOT NULL);
CREATE TABLE [structure].[ItemEvent] ([Id] UNIQUEIDENTIFIER NOT NULL, [ItemId] UNIQUEIDENTIFIER NOT NULL, [Version] INT NULL, [EventDate] DATETIME DEFAULT (getdate()) NOT NULL, [EventType] VARCHAR (50) NOT NULL, [Message] VARCHAR (512) NULL);
CREATE TABLE [structure].[ItemRelationship] ([ItemId] UNIQUEIDENTIFIER NOT NULL, [Version] INT NOT NULL, [RelationshipType] VARCHAR (50) NOT NULL, [TargetItem] VARCHAR (512) NOT NULL, [State] VARCHAR (1024) NULL);
--CREATE TABLE [structure].[ItemResource] ([ItemId] UNIQUEIDENTIFIER NOT NULL, [Version] INT NOT NULL, [FileExtension] VARCHAR (10) NULL, [Resource] VARCHAR (MAX) NULL, [RowIdentifier] VARCHAR(MAX) NULL);
CREATE TABLE [structure].[ItemResource] ([ItemId] UNIQUEIDENTIFIER NOT NULL, [Version] INT NOT NULL, [Resource] VARCHAR (MAX) NULL, [RowIdentifier] VARCHAR(MAX) NULL);
CREATE TABLE [structure].[ItemVersion] ([ItemId] UNIQUEIDENTIFIER NOT NULL, [Version] INT NOT NULL, [LeftVersion] INT NULL, [RightVersion] INT NULL, [Comment] VARCHAR (255) NULL, [Name] VARCHAR (255) NOT NULL, [MimeType] VARCHAR (50) NOT NULL, [SpecificationId] UNIQUEIDENTIFIER NOT NULL, [CreatedBy] VARCHAR (255) NOT NULL, [LastModified] DATETIME NOT NULL, [PropertyData] VARBINARY (MAX)  NULL, [RowIdentifier] VARCHAR(MAX) NULL);
CREATE INDEX ItemColumnIndex ON [structure].[Item] (Id ASC, ParentId ASC);
CREATE INDEX ItemVersionColumnIndex ON [structure].[ItemVersion] (ItemId ASC, Name ASC);

-- Setup a set of permissions to use as an example.  In this case Administrators have full access, Users have Read Only and Guests have none.
INSERT INTO [structure].[ItemClaim] (Id, ClaimType, ClaimValue, AccessFlags) VALUES ('7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'http://schemas.microsoft.com/ws/2008/06/identity/claims/role', 'Administrators', 511);
INSERT INTO [structure].[ItemClaim] (Id, ClaimType, ClaimValue, AccessFlags) VALUES ('7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'http://schemas.microsoft.com/ws/2008/06/identity/claims/role', 'Users', 7);
INSERT INTO [structure].[ItemClaim] (Id, ClaimType, ClaimValue, AccessFlags) VALUES ('7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'http://schemas.microsoft.com/ws/2008/06/identity/claims/role', 'Guest', 0);

-- Create a default set of items to work with. (Uses claim set above).
INSERT INTO [structure].[Item] (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href) VALUES ('7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', null, null, 1, '7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'col://root');
INSERT INTO [structure].[Item] (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href) VALUES ('7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', '7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 'ExternalId', 1, '7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'col://root/folder');
INSERT INTO [structure].[Item] (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href) VALUES ('7E117A1E-FEED-DEAD-BEEF-64A46028EC14', '7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', null, 1, '7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'col://root/file1');
INSERT INTO [structure].[Item] (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href) VALUES ('7E117A1E-FEED-DEAD-BEEF-1215B4695F56', '7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', null, 1, '7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'col://root/folder/file2');
INSERT INTO [structure].[Item] (Id, ParentId, ExternalId, PublishedVersion, ItemClaimId, Href) VALUES ('7E117A1E-FEED-DEAD-BEEF-3E485FD654AB', '7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', null, 1, '7E117A1E-FEED-DEAD-BEEF-0A7A9D9ED396', 'col://root/folder/file3');

-- Add some metadata to each item.
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 1, null, null, 'Default Database Example', 'root', 'application/x-folder', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:00');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 2, 1, 1, 'Default Database Example', 'root2', 'application/x-folder', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:00');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', 1, null, null, 'Default Database Example', 'folder', 'application/x-folder', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:01');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-64A46028EC14', 1, null, null, 'Default Database Example', 'file1', 'text/html', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:02');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-1215B4695F56', 1, null, null, 'Default Database Example', 'file2', 'text/html', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:03');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-1215B4695F56', 2, 1, null, 'Default Database Example', 'file2', 'text/text', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:03');
INSERT INTO [structure].[ItemVersion] (ItemId, Version, LeftVersion, RightVersion, Comment, Name, MimeType, SpecificationId, CreatedBy, LastModified) VALUES ('7E117A1E-FEED-DEAD-BEEF-3E485FD654AB', 1, null, null, 'Default Database Example', 'file3', 'text/html', '7E117A1E-FEED-DEAD-BEEF-E8EEEF89FC35', 'System', '1/1/2010 12:00:03');

-- Add some logging events.
INSERT INTO [structure].[ItemEvent] (Id, ItemId, Version, EventDate, EventType, Message) VALUES ('7E117A1E-FEED-DEAD-BEEF-000000000001', '7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 1, '1/1/2010 12:00:00', 'Item Created', 'A test item was created by the database build script')
INSERT INTO [structure].[ItemEvent] (Id, ItemId, Version, EventDate, EventType, Message) VALUES ('7E117A1E-FEED-DEAD-BEEF-000000000002', '7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 2, '1/1/2010 12:00:00', 'Item Edited', 'A test item was edit to add a version by the database build script')
INSERT INTO [structure].[ItemEvent] (Id, ItemId, Version, EventDate, EventType, Message) VALUES ('7E117A1E-FEED-DEAD-BEEF-000000000003', '7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', 1, '1/1/2010 12:00:01', 'Item Created', 'A test item was created by the database build script')
INSERT INTO [structure].[ItemEvent] (Id, ItemId, Version, EventDate, EventType, Message) VALUES ('7E117A1E-FEED-DEAD-BEEF-000000000004', '7E117A1E-FEED-DEAD-BEEF-64A46028EC14', 1, '1/1/2010 12:00:02', 'Item Created', 'A test item was created by the database build script')
INSERT INTO [structure].[ItemEvent] (Id, ItemId, Version, EventDate, EventType, Message) VALUES ('7E117A1E-FEED-DEAD-BEEF-000000000005', '7E117A1E-FEED-DEAD-BEEF-1215B4695F56', 1, '1/1/2010 12:00:03', 'Item Created', 'A test item was created by the database build script')

-- Add an example relationship between the 'files'
INSERT INTO [structure].[ItemRelationship] (ItemId, Version, RelationshipType, TargetItem, State) VALUES ('7E117A1E-FEED-DEAD-BEEF-64A46028EC14', 1, 'Association', 'col://root/folder/file2', null);
INSERT INTO [structure].[ItemRelationship] (ItemId, Version, RelationshipType, TargetItem, State) VALUES ('7E117A1E-FEED-DEAD-BEEF-64A46028EC14', 1, 'Association', 'col://root/folder/file3', 'state');

-- File in the item resources
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 1, null);
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-8CDE4058E989', 2, null);
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-E3E5DE32913A', 1, null);
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-64A46028EC14', 1, '<html><head><title>File 1</title></head><body><h1>File 1</h1></body></html>');
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-1215B4695F56', 1, '<html><head><title>File 2</title></head><body><h1>File 2</h1></body></html>');
INSERT INTO [structure].[ItemResource] (ItemId, Version, Resource) VALUES ('7E117A1E-FEED-DEAD-BEEF-3E485FD654AB', 1, '<html><head><title>File 2</title></head><body><h1>File 2</h1></body></html>');

IF OBJECT_ID (N'structure.GetLatestVersion', N'FN') IS NOT NULL DROP FUNCTION structure.GetLatestVersion; 
CREATE FUNCTION structure.GetLatestVersion (@itemId uniqueIdentifier) RETURNS INT AS BEGIN RETURN (SELECT max(ItemVersion.Version) FROM structure.ItemVersion WHERE (ItemVersion.ItemId = @itemId))END;

IF OBJECT_ID (N'structure.GetItemPath', N'FN') IS NOT NULL DROP FUNCTION structure.GetItemPath; 
CREATE FUNCTION structure.GetItemPath (@itemId uniqueidentifier, @version int ) RETURNS varchar(500) AS BEGIN DECLARE @Path varchar(500) ;WITH PathParts (ParentId, ItemId, ObjectPath) AS ( SELECT Item.ParentId, Item.Id, CAST(ItemVersion.Name as varchar(255)) FROM structure.Item INNER JOIN structure.ItemVersion ON (Item.Id = ItemVersion.ItemId) WHERE Item.Id = @itemId AND (ItemVersion.Version = @version) UNION ALL SELECT Item.ParentId, Item.Id, CAST (ItemVersion.Name + '/' + PathParts.ObjectPath AS varchar(255)) FROM structure.Item INNER JOIN structure.ItemVersion ON (Item.Id = ItemVersion.ItemId) INNER JOIN PathParts ON Item.Id = PathParts.ParentId WHERE (ItemVersion.Version =  Item.PublishedVersion)) SELECT @Path =  'col://' + (objectpath ) FROM PathParts WHERE ParentId IS NULL RETURN @Path END;

COMMIT;